﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Rison.DataAccess.Common;
using Rison.DBHelper;

namespace Rison.DataAccess.Data.Biz
{
    public sealed class InfosReaderDb : DataAccessBase
    {
        public InfosReaderDb()
            : base()
        {
        }

        public InfosReaderDb(string tempDbConnStr)
            : base()
        {
            //设置第三方数据库的连接串
            base.SetTempConnectionString(tempDbConnStr);
        }

        protected override string tableName
        {
            get { return "Biz_CPResultDetail"; }
        }

        protected override string IdentityFieldName
        {
            get { return "RDID"; }
        }

        public DataRow ReadInfomation(string infoId)
        {
            return base.ExecuteSqlToDataRow("select title,showTime,content,MediaFileName from Information where infoGuid='" + infoId + "'");
        }

        public DataSet ReadAttachment(string infoId)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select b.* from InformationAttachmentRelation a, Attachment b where a.attachmentid=b.attachmentid and a.infoGuid='" + infoId + "'");
            return base.ExecuteSql(sql.ToString(), "ReadAttachment");
        }

        public DataSet ReadNewsInfos(string categoryId, int topNum, int subjectLength, bool containContent)
        {
            return ReadNewsInfos(categoryId, topNum, subjectLength, containContent, string.Empty);
        }

        public DataSet ReadNewsInfos(string categoryId, int topNum, int subjectLength, bool containContent,string condition)
        {
            StringBuilder sql = new StringBuilder();
            if (topNum > 1)
                sql.AppendFormat("select top {0} infoGuid as infoId,ShowTime as day,MediaFileName,", topNum.ToString());
            else
                sql.Append("select infoGuid as infoId,ShowTime as day,MediaFileName,");

            if (containContent)
                sql.Append("ContentNoHTML,");

            //sql.AppendFormat("title=case when (datalength(title)>{0}*2) then dbo.fn_substringSubject(title,{1}) else title end ", (subjectLength).ToString(), (subjectLength - 1).ToString());
            if (subjectLength > 1)
                sql.AppendFormat("title=case when (datalength(title)>{0}*2) then SUBSTRING(title,1,{1})+'..' else title end ", (subjectLength).ToString(), (subjectLength - 1).ToString());
            else
                sql.Append("title ");

            sql.AppendFormat("from vwInfoCategory_Show where CategoryID='{0}' ", categoryId);

            if (!string.IsNullOrEmpty(condition))
                sql.AppendFormat(" and {0} ", condition);

            sql.Append("Order by BackInt,ShowTime DESC,CreateTime DESC");

            DataSet data = ExecuteSql(sql.ToString(), "NewsInfos");

            sql = null; return data;
        }



        public DataRow ReadSuqiuDetail(string requestId)
        {
            return base.ExecuteSqlToDataRow("select * from  dbo.InformationCategoryRelation a INNER JOIN dbo.Information b ON a.InfoGuid = b.InfoGuid  where b.infoguid='" + requestId + "'");
        }



        //最新诉求 --IsOpen是是否公布咨询投诉的内容 
        public DataSet ReadSuqiuInfos(int topNum, int subjectLength, string categoryId)
        {
            StringBuilder sql = new StringBuilder();

            if (topNum > 1)
                //sql.AppendFormat("select top {0} ID as infoId,Type,SubmitTime as day,", topNum.ToString());
                sql.AppendFormat("select top {0} infoguid as infoID,subtitle as Type,showtime as day, ", topNum.ToString());
            else
                //sql.Append("select ID as infoId,Type,SubmitTime as day,");
                sql.AppendFormat("select infoguid as infoID,subtitle as Type,showtime as day, ");


            if (subjectLength > 1)
                sql.AppendFormat("title=case when (datalength(title)>{0}*2) then SUBSTRING(title,1,{1})+'..' else title end ", (subjectLength).ToString(), (subjectLength - 1).ToString());
            else
                sql.Append("title ");

            //sql.Append("from Request where requestType='我有诉求' and IsOpen=1 Order by SubmitTime DESC");
            sql.AppendFormat("from vwInfoCategory_Show where CategoryID='{0}' Order by BackInt,ShowTime DESC,CreateTime DESC ", categoryId);


            DataSet data = ExecuteSql(sql.ToString(), "SuqiuInfos");

            sql = null; return data;
        }


        //诉求的最新回复 -- ispublish是信件处理完毕后是否公开到前台
        public DataSet ReadSuqiuRepeat(int topNum, int subjectLength, string categoryId)
        {
            StringBuilder sql = new StringBuilder();

            if (topNum > 1)
                //sql.AppendFormat("select top {0} ID as infoId,Type,CompleteTime as day,", topNum.ToString());
                sql.AppendFormat("select top {0} infoguid as infoID,subtitle as Type,BackNvarchar4 as day, ", topNum.ToString());

            else
                sql.AppendFormat("select infoguid as infoID,subtitle as Type,BackNvarchar4 as day,");

            if (subjectLength > 1)
                sql.AppendFormat("title=case when (datalength(title)>{0}*2) then SUBSTRING(title,1,{1})+'..' else title end ", (subjectLength).ToString(), (subjectLength - 1).ToString());
            else
                sql.Append("title ");

            //sql.Append("from Request where requestType='我有诉求' and ispublished=1 ");
            sql.AppendFormat("from vwInfoCategory_Show where CategoryID='{0}' Order by day desc ,BackInt desc,ShowTime DESC ", categoryId);
           

            DataSet data = ExecuteSql(sql.ToString(), "SuqiuRepeat");

            sql = null; return data;

        }

        public DataSet ReadVoteAnswer(string id)
        {
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat("select AnswerContent,VoteCount from ViewVote where VoteID='{0}' order by VoteCount desc", id);
            DataSet data = ExecuteSql(sql.ToString(), "ReadVoteAnswer");

            sql = null; return data;

        }

        //诉求的list表
        public DataSet ReadSuqiuList(string condition, string categoryId)
        {
            StringBuilder sql = new StringBuilder();

            //sql.Append("select a.id,a.title,a.SubmitTime,a.Type,b.MailBoxDisplay,a.CompleteTime from Request a ");
            //sql.Append("left outer join MailBox b on a.SendToTag=b.ID ");
            //sql.Append("where a.RequestType='我有诉求' and a.isopen=1 ");
            //where 
            sql.Append("select infoguid as id,title,subtitle as type,Source as MailBoxDisplay,Description,showtime as SubmitTime,BackNvarchar4 as CompleteTime");

            sql.AppendFormat(" from vwInfoCategory_Show where CategoryID='{0}' ", categoryId);
            if (condition != "")
            {
                sql.Append(condition);
            }
            //排序 -按提交时间
            sql.Append(" order by showtime desc");

            DataSet data = ExecuteSql(sql.ToString(), "ReadSuqiuList");

            sql = null; return data;
        }

        //诉求回复
        public DataSet SuQiuTJ(string categoryId)
        {
            StringBuilder sql = new StringBuilder();

            //sql.Append("select MailBoxDisplay,zx,ts,jy,sq, ");
            //sql.Append("(select COUNT(1) from Request c where c.SendToTag=d.SendToTag and c.ispublished=1) as hf ");
            //sql.Append("from  ");
            //sql.Append("(select a.SendToTag, ");
            //sql.Append("count(case when a.type='咨询' then a.id end) zx, ");
            //sql.Append("count(case when a.type='投诉' then a.id end) ts, ");
            //sql.Append("count(case when a.type='意见建议' then a.id end) jy, ");
            //sql.Append("count(1) sq ");
            //sql.Append("from Request a where a.RequestType='我有诉求' and a.isopen=1 ");
            //sql.Append("group by  a.SendToTag) d right join MailBox m on d.SendToTag=m.ID  ");
            //sql.Append("inner join CategoryMailboxRel cat on m.ID = cat.MailBOxID  ");
            //sql.Append("inner join Category on cat.CategoryID = Category.CategoryID  ");
            //sql.Append(" where category.ParentCategoryID='5B1D83C8-18BF-40CB-A212-CB9E48F3B158'");
            ////sql.Append("where Category.CategoryName='行业' ");
            ////sql.Append("or Category.CategoryName='政府部门' ");
            //sql.Append("order by Category.OrderNum,cat.OrderNum ");

            sql.Append("select e.displayname as MailBoxDisplay,zx,ts,jy,sq, ");
            sql.AppendFormat("(select COUNT(1) from vwInfoCategory_Show c where c.source=e.groupname and c.CategoryID='{0}') as hf ", categoryId);
            sql.Append("from  ");
            sql.Append("(select a.source, ");
            sql.Append("count(case when a.subtitle='咨询' then a.infoguid end) zx, ");
            sql.Append("count(case when a.subtitle='投诉' then a.infoguid end) ts, ");
            sql.Append("count(case when a.subtitle='意见建议' then a.infoguid end) jy, ");
            sql.Append("count(1) sq ");
            sql.AppendFormat("from vwInfoCategory_Show a where a.CategoryID='{0}' ", categoryId);
            sql.Append("group by  a.Source) d ");
            sql.Append("right join  groups_jf e on d.source=e.groupname where e.flag =1 order by hf desc");
            //sql.Append("where Category.CategoryName='行业' ");
            //sql.Append("or Category.CategoryName='政府部门' ");
            //sql.Append("order by Category.OrderNum,cat.OrderNum ");


            DataSet data = ExecuteSql(sql.ToString(), "SuQiuTJ");

            sql = null; return data;
        }

        /// <summary>
        /// 按部门名称排序，disable 2011-04-28
        /// </summary>
        /// <param name="topNum"></param>
        /// <param name="subjectLength"></param>
        /// <returns></returns>
        //public DataSet SuQiuTJ_show(int topNum, int subjectLength)
        //{
        //    StringBuilder sql = new StringBuilder();

        //    sql.AppendFormat("select top {0} m.id, " ,topNum.ToString());
        //    sql.Append("case m.ID ");
        //    sql.Append("when '917E7DB2-7DE2-49ED-8326-5C3BD6C2B369' then '区府办' ");
        //    sql.Append("when 'A4BA9348-3EC6-4761-AD8B-BE999976B436' then '区发改委' ");
        //    sql.Append("when '4B8E1D34-0414-4F9E-91B3-C3FCCA4841CE' then '区商务委' ");
        //    sql.Append("when '6018F75E-F67E-4B2C-B1F1-7BE2309D62FC' then '区建交委' ");
        //    sql.Append("when 'C3610045-99EC-4D23-9B8C-7444941EEC53' then '区科委' ");
        //    sql.Append("when '1C94C597-5316-4E28-87A5-A923B3B94952' then '区人口计生委' ");
        //    sql.Append("when '7CA71981-55D1-4427-A319-8301601000A0' then '区国资委' ");
        //    sql.Append("else MailBoxDisplay ");
        //    sql.Append("end as MailBoxDisplay, ");
        //    //sql.Append(" MailBoxDisplay, ");
        //    sql.Append("case when sq is null then 0 else sq end sq,Category.OrderNum,cat.OrderNum, ");
        //    sql.Append(" (select COUNT(1) from Request c where c.SendToTag=d.SendToTag and CompleteTime!='') as hf  ");
        //    sql.Append("from (select a.SendToTag, count(1) as sq  ");
        //    sql.Append(" from Request a where a.RequestType='我有诉求' and a.ispublished=1 group by  a.SendToTag) d  ");
        //   sql.Append(" right outer join MailBox m on d.SendToTag=m.ID   ");
        //   sql.Append(" inner join CategoryMailboxRel cat on m.ID = cat.MailBOxID   ");
        //   sql.Append(" inner join Category on cat.CategoryID = Category.CategoryID       ");
        //   sql.Append(" where category.ParentCategoryID='5B1D83C8-18BF-40CB-A212-CB9E48F3B158'    ");
        //   sql.Append(" order by Category.OrderNum,cat.OrderNum  ");


        //    DataSet data = ExecuteSql(sql.ToString(), "SuQiuTJ");

        //    sql = null; return data;
        //}

        /// <summary>
        /// 按回复件排序
        /// </summary>
        /// <param name="topNum"></param>
        /// <param name="subjectLength"></param>
        /// <returns></returns>
        public DataSet SuQiuTJ_show(int topNum, int subjectLength, string categoryId)
        {
            StringBuilder sql = new StringBuilder();

            //sql.AppendFormat("select top {0} m.id, ", topNum.ToString());

            ////sql.Append(" MailBoxDisplay, ");
            //if (subjectLength > 1)
            //    sql.AppendFormat("case when (datalength(MailBoxDisplay)>{0}*2) then SUBSTRING(MailBoxDisplay,1,{1})+'..' else MailBoxDisplay end MailBoxDisplay, ", (subjectLength).ToString(), (subjectLength - 1).ToString());
            //else
            //    sql.Append("MailBoxDisplay ");

            //sql.Append("case when sq is null then 0 else sq end sq,Category.OrderNum,cat.OrderNum, ");
            //sql.Append(" (select COUNT(1) from Request c where c.SendToTag=d.SendToTag and ispublished=1) as hf  ");
            //sql.Append("from (select a.SendToTag, count(1) as sq  ");
            //sql.Append(" from Request a where a.RequestType='我有诉求' and a.isopen=1 group by  a.SendToTag) d  ");
            //sql.Append(" right outer join MailBox m on d.SendToTag=m.ID   ");
            //sql.Append(" inner join CategoryMailboxRel cat on m.ID = cat.MailBOxID   ");
            //sql.Append(" inner join Category on cat.CategoryID = Category.CategoryID       ");
            //sql.Append(" where category.ParentCategoryID='5B1D83C8-18BF-40CB-A212-CB9E48F3B158'    ");
            //sql.Append(" order by hf desc,sq desc,Category.OrderNum,cat.OrderNum ");


            sql.AppendFormat("select top {0}  ", topNum.ToString());

            //sql.Append(" MailBoxDisplay, ");
            if (subjectLength > 1)
                sql.AppendFormat("case when (datalength(displayname)>{0}*2) then SUBSTRING(displayname,1,{1})+'..' else displayname end MailBoxDisplay, ", (subjectLength).ToString(), (subjectLength - 1).ToString());
            else
                sql.Append("displayname as MailBoxDisplay ");

            sql.Append("case when sq is null then 0 else sq end sq, ");
            sql.AppendFormat(" (select COUNT(1) from vwInfoCategory_Show c where c.source=e.groupname and  CategoryID='{0}') as hf  " ,categoryId);
            sql.Append("from (select a.Source, count(1) as sq  from ");
            sql.AppendFormat("vwInfoCategory_Show a where CategoryID='{0}' group by  a.Source) d  ", categoryId);
            sql.Append("right join  groups_jf e on d.source=e.groupname where e.flag=1 ");
            sql.Append(" order by hf desc,sq desc,e.sortno");

                   
            DataSet data = ExecuteSql(sql.ToString(), "SuQiuTJ");

            sql = null; return data;
        }

    }
}